package tg.edu.fhh.demo.service;



import java.util.List;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import tg.edu.fhh.demo.custom.BaseService;
import tg.edu.fhh.demo.entity.Course;
import tg.edu.fhh.demo.entity.Grade;
import tg.edu.fhh.demo.repository.GradeRepository;



@Service
public class GradeService extends BaseService<Grade, Integer> {

	@Autowired
	private GradeRepository gradeDAO;
	@Resource
	private EntityManager entityManager;
	
	@SuppressWarnings("unchecked")
	public List<List<Object>> getCjb(Integer bjId, List<Course> courses) {
		String hql="select g.student.stuNo,g.student.name ";
		for (Course course : courses) {
			hql+=",sum(case when g.course.id="+course.getId()+" then g.grade else 0 end)";
		}
		hql+=",sum(g.grade) as total";  
		hql+=" from Grade g";
		hql+=" where g.student.tbClass.id="+bjId;
		hql+=" group by g.student.id";
		hql+=" order by total desc";
		Query query = entityManager.createQuery(hql);
		entityManager.close();
		return query.getResultList();
	}
	
	public List<Course> findCourseByTbClassId(Integer bjId) {
		return gradeDAO.findCourseByTbClassId(bjId);
	}
	public Grade findByStudentIdAndCourseId(Integer id, Integer kcId) {
		return gradeDAO.findByStudentIdAndCourseId(id,kcId);
	}
	public List<Grade> findByStudentId(Integer id) {
		return gradeDAO.findByStudentId(id);
	}

	}

	
